﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using AG.Utilities;

namespace SQLServerHealthCheck.SQLServer
{


    public class CollectSQLAgentData
    {
        List<Exception> errors;
        HealthCheckDataContext dc;
        SQLServerInstance sqlServerInstance;
        

        public List<Exception> Collect(SQLServerInstance SqlServerInstance)
        {
            try
            {
                sqlServerInstance = SqlServerInstance;

                errors = new List<Exception>();

                dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                FillSQLJobDetails();
                FillMaintenancePlanDetails();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            return errors;

        }


       public void FillSQLJobDetails()
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " FillSQLJobDetails method in SQLAgentCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                {

                    String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                    String serverName = parts[0];


                    Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                    if (server != null)
                    {

                        Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                        if (service != null)
                        {




                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();

                                string sqlOLE1 = "sp_configure 'Agent XPs',1 ;reconfigure;";
                                SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                                OLEcmd1.ExecuteNonQuery();


                                String sql = @"	

CREATE TABLE #Temp1
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
INSERT INTO #Temp1
 
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE #Temp1
SET last_run_time    = right ('000000' + last_run_time, 6),
next_run_time    = right ('000000' + next_run_time, 6);







SELECT j.name AS JobName,
case j.enabled
when 1 then 'Enabled'
else 'Disabled'
end
 AS Enabled,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunStatus,
c.name as Category,
CAST(
convert(datetime,cast(h.run_date as varchar))
+(cast(h.run_time as float)*(.36)+cast(h.run_time%10000 as float)*(.6-.36)+cast(h.run_time%100 as float)*(1-.6)+ run_duration)/86400

as varchar) as LastRunDate,

CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS [Duration(ms)]


FROM         #Temp1 x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON     x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0

drop table #Temp1
";



                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();
                                int i = 0;

                                while (rd.Read())
                                {
                                    Model.SQLAgentJobDetail ajd = null;

                                    ajd = new SQLAgentJobDetail();
                                    i++;
                                    if (service.SQLAgentJobDetails.Count(d => d.JobName == rd[1].ToString()) == 0)
                                    {
                                        

                                        ajd.JobName = rd[0].ToString();
                                        ajd.JobID = i;
                                        ajd.Enabled = rd[1].ToString();
                                        ajd.LastRunStatus = rd[2].ToString();
                                        ajd.Category = rd[3].ToString();
                                        ajd.LastRunDateTime = rd[4].ToString();
                                        ajd.Duration = rd.IsDBNull(5) ? (int?)null : Convert.ToInt32(rd[5].ToString());

                                        service.SQLAgentJobDetails.Add(ajd);
                                    }
                                    else
                                    {
                                        ajd = service.SQLAgentJobDetails.First(d => d.JobName == rd[1].ToString());
                                    }
                                }


                                rd.Close();


                            }


                            dc.SubmitChanges();
                            // InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " FillSQLJobDetails method in SQLAgentCheck Completed for " + service.InstanceName.ToString());

                        }
                    }
                }


            }


            catch (Exception ex)
            {

                Logger.Error(ex);
                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " Some error occured in SQLAgentCheck.Please check exception file for details");


            }



        }


        private void FillMaintenancePlanDetails()
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " FillSQLJobDetails method in SQLAgentCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                {
                    String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                    String serverName = parts[0];
                    Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);
                    string sql = "";
                    if (server != null)
                    {
                        Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                        if (service != null)
                        {
                            if (
                                service.ProductVersion.StartsWith("10.0")
                                    ||
                                service.ProductVersion.StartsWith("10.5")
                                    ||
                                service.ProductVersion.StartsWith("11")

                            )
                            { sql = @"	

select
	CAST(PK.name as nvarchar(100)) AS MaintenancePlan
	,CAST(SP.subplan_name as nvarchar(100)) AS SubPlan,
	CAST(SP.job_id as nvarchar(100)) AS JobId,
	CAST(J.name as nvarchar(100)) AS JobName
,'Enabled'      = CASE (S.enabled)
                  WHEN 0 THEN 'No'
                  WHEN 1 THEN 'Yes'
                  ELSE '??'
                END,
'Frequency'    = CASE(S.freq_type)
                  WHEN 1  THEN 'Once'
                  WHEN 4  THEN 'Daily'
                  WHEN 8  THEN 
                    (case when (S.freq_recurrence_factor > 1) 
                        then  'Every ' + convert(varchar(3),S.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
                  WHEN 16 THEN 
                    (case when (S.freq_recurrence_factor > 1) 
                    then  'Every ' + convert(varchar(3),S.freq_recurrence_factor) + ' Months' else 'Monthly' end)
                  WHEN 32 THEN 'Every ' + convert(varchar(3),S.freq_recurrence_factor) + ' Months' -- RELATIVE
                  WHEN 64 THEN 'SQL Startup'
                  WHEN 128 THEN 'SQL Idle'
                  ELSE '??'
                END,
'Interval'    = CASE
                 WHEN (freq_type = 1)                       then 'One time only'
                 WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
                 WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
                 WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7 
                       from (select top 1 S.schedule_id,
                     freq_interval, 
                     'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,
                     'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,
                     'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,
                     'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,
                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                 from msdb..sysschedules ss
                                where freq_type = 8
                           ) as F
                       where schedule_id = JS.schedule_id
                                            )
                 WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval) 
                 WHEN (freq_type = 32) then (select freq_rel + WDAY 
                    from (select S.schedule_id,
                                 'freq_rel' = CASE(freq_relative_interval)
                                                WHEN 1 then 'First'
                                                WHEN 2 then 'Second'
                                                WHEN 4 then 'Third'
                                                WHEN 8 then 'Fourth'
                                                WHEN 16 then 'Last'
                                                ELSE '??'
                                              END,
                                'WDAY'     = CASE (freq_interval)
                                                WHEN 1 then ' Sun'
                                                WHEN 2 then ' Mon'
                                                WHEN 3 then ' Tue'
                                                WHEN 4 then ' Wed'
                                                WHEN 5 then ' Thu'
                                                WHEN 6 then ' Fri'
                                                WHEN 7 then ' Sat'
                                                WHEN 8 then ' Day'
                                                WHEN 9 then ' Weekday'
                                                WHEN 10 then ' Weekend'
                                                ELSE '??'
                                              END
                            from msdb..sysschedules ss
                            where S.freq_type = 32
                         ) as WS 
                   where WS.schedule_id =S.schedule_id
                   ) 
               END,
'Time' = CASE (freq_subday_type)
                WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
                WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
                WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
                WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
                ELSE '??'
              END,

'Next Run Time' = CASE JS.next_run_date
                   WHEN 0 THEN NULL
                   ELSE CAST(convert(char(10), convert(datetime, convert(char(8),JS.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8) AS datetime)
                 END
  	,CAST(CAST([packagedata] as varbinary(max)) as varchar(max)) AS PackageSource
	,CAST(CASE
		WHEN CHARINDEX(  'DbMaintenanceReindexTask' , CAST(CAST([packagedata] as varbinary(max)) as varchar(max)))>0 THEN 1
		ELSE 0
	END AS bit) AS HasReBuildIndexTask
	, 
	CAST(CASE
		WHEN CHARINDEX(  'DbMaintenanceDefragmentIndexTask' , CAST(CAST([packagedata] as varbinary(max)) as varchar(max)))>0 THEN 1
		ELSE 0
	END AS bit) AS HasReorganizeIndexTask
	,
	CAST(CASE
		WHEN CHARINDEX(  'DbMaintenanceShrinkTask' , CAST(CAST([packagedata] as varbinary(max)) as varchar(max)))>0 THEN 1
		ELSE 0
	END AS bit) AS HasAutoShrinkTask

   from 
   	msdb.[dbo].[sysmaintplan_subplans] SP
		INNER JOIN
	msdb.[dbo].[sysssispackages] PK ON PK.id= SP.plan_id
		INNER JOIN
	msdb.dbo.sysjobs J  on J.job_id = SP.job_id		
		INNER JOIN 
	msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id 
		INNER JOIN
	msdb.dbo.sysschedules S on S.schedule_id = JS.schedule_id
	where j.job_id = SP.job_id
order by S.name



" ;}
                            else
                            { sql = @"select
	CAST(PK.name as nvarchar(100)) AS MaintenancePlan
	,CAST(SP.subplan_name as nvarchar(100)) AS SubPlan,
	CAST(SP.job_id as nvarchar(100)) AS JobId,
	CAST(J.name as nvarchar(100)) AS JobName
,'Enabled'      = CASE (S.enabled)
                  WHEN 0 THEN 'No'
                  WHEN 1 THEN 'Yes'
                  ELSE '??'
                END,
'Frequency'    = CASE(S.freq_type)
                  WHEN 1  THEN 'Once'
                  WHEN 4  THEN 'Daily'
                  WHEN 8  THEN 
                    (case when (S.freq_recurrence_factor > 1) 
                        then  'Every ' + convert(varchar(3),S.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
                  WHEN 16 THEN 
                    (case when (S.freq_recurrence_factor > 1) 
                    then  'Every ' + convert(varchar(3),S.freq_recurrence_factor) + ' Months' else 'Monthly' end)
                  WHEN 32 THEN 'Every ' + convert(varchar(3),S.freq_recurrence_factor) + ' Months' -- RELATIVE
                  WHEN 64 THEN 'SQL Startup'
                  WHEN 128 THEN 'SQL Idle'
                  ELSE '??'
                END,
'Interval'    = CASE
                 WHEN (freq_type = 1)                       then 'One time only'
                 WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
                 WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
                 WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7 
                       from (select top 1 S.schedule_id,
                     freq_interval, 
                     'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,
                     'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,
                     'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,
                     'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,
                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                 from msdb..sysschedules ss
                                where freq_type = 8
                           ) as F
                       where schedule_id = JS.schedule_id
                                            )
                 WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval) 
                 WHEN (freq_type = 32) then (select freq_rel + WDAY 
                    from (select S.schedule_id,
                                 'freq_rel' = CASE(freq_relative_interval)
                                                WHEN 1 then 'First'
                                                WHEN 2 then 'Second'
                                                WHEN 4 then 'Third'
                                                WHEN 8 then 'Fourth'
                                                WHEN 16 then 'Last'
                                                ELSE '??'
                                              END,
                                'WDAY'     = CASE (freq_interval)
                                                WHEN 1 then ' Sun'
                                                WHEN 2 then ' Mon'
                                                WHEN 3 then ' Tue'
                                                WHEN 4 then ' Wed'
                                                WHEN 5 then ' Thu'
                                                WHEN 6 then ' Fri'
                                                WHEN 7 then ' Sat'
                                                WHEN 8 then ' Day'
                                                WHEN 9 then ' Weekday'
                                                WHEN 10 then ' Weekend'
                                                ELSE '??'
                                              END
                            from msdb..sysschedules ss
                            where S.freq_type = 32
                         ) as WS 
                   where WS.schedule_id =S.schedule_id
                   ) 
               END,
'Time' = CASE (freq_subday_type)
                WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
                WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
                WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
                WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
                ELSE '??'
              END,

'Next Run Time' = CASE JS.next_run_date
                   WHEN 0 THEN NULL
                   ELSE CAST(convert(char(10), convert(datetime, convert(char(8),JS.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8) AS datetime)
                 END
  	,CAST(CAST([packagedata] as varbinary(max)) as varchar(max)) AS PackageSource
	,CAST(CASE
		WHEN CHARINDEX(  'DbMaintenanceReindexTask' , CAST(CAST([packagedata] as varbinary(max)) as varchar(max)))>0 THEN 1
		ELSE 0
	END AS bit) AS HasReBuildIndexTask
	, 
	CAST(CASE
		WHEN CHARINDEX(  'DbMaintenanceDefragmentIndexTask' , CAST(CAST([packagedata] as varbinary(max)) as varchar(max)))>0 THEN 1
		ELSE 0
	END AS bit) AS HasReorganizeIndexTask
	,
	CAST(CASE
		WHEN CHARINDEX(  'DbMaintenanceShrinkTask' , CAST(CAST([packagedata] as varbinary(max)) as varchar(max)))>0 THEN 1
		ELSE 0
	END AS bit) AS HasAutoShrinkTask

   from 
   	msdb.[dbo].[sysmaintplan_subplans] SP
		INNER JOIN
	msdb.[dbo].[sysdtspackages90] PK ON PK.id= SP.plan_id
		INNER JOIN
	msdb.dbo.sysjobs J  on J.job_id = SP.job_id		
		INNER JOIN 
	msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id 
		INNER JOIN
	msdb.dbo.sysschedules S on S.schedule_id = JS.schedule_id
	where j.job_id = SP.job_id
order by S.name";
                            }

                            
                        
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();

                                string sqlOLE1 = "sp_configure 'Agent XPs',1 ;reconfigure;";
                                SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                                OLEcmd1.ExecuteNonQuery();


                                
                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();

                                while (rd.Read())
                                {

                                    MaintenanceSubPlan ajd = null;

                                    ajd = new MaintenanceSubPlan();

                                    ajd.MaintenancePlan = rd[0].ToString();
                                    ajd.SubPlan = rd[1].ToString();
                                    ajd.Enabled = rd[4].ToString();
                                    ajd.Frequency = rd[5].ToString();
                                    ajd.Interval = rd[6].ToString();
                                    ajd.Time = rd[7].ToString();
                                    ajd.Next_Run_Time = rd.IsDBNull(8) ? (DateTime?)null : rd.GetDateTime(8);
                                    ajd.PackageSource = rd[9].ToString();
                                    ajd.HasReBuildIndexTask = rd.GetBoolean(10);
                                    ajd.HasReorganizeIndexTask = rd.GetBoolean(11);
                                    ajd.HasAutoShrinkTask = rd.GetBoolean(12);

                                    string jobname = rd[3].ToString();
                                    SQLAgentJobDetail jobdetail = dc.SQLAgentJobDetails.FirstOrDefault(j => j.JobName == jobname);
                                    ajd.SQLAgentJobDetail = jobdetail;

                    
                                    service.MaintenanceSubPlans.Add(ajd);
                                    
                                }
                                rd.Close();

                                dc.SubmitChanges();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {

                Logger.Error(ex);
                errors.Add(ex);
            }
        }
    }
}
